package daos

import (
	"bsm/logger"
	"bsm/models"
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
)

type ScmDao interface {
	GetMaterialPurchasePrice(materialPrices []models.MaterialPrice) (int, error)                   // 获取预算材料单价（市公司采购成本价格）
	GetMaterialBalancePrice(pkorg string, materialPrices []models.MaterialPrice) (int, error)      // 获取结算材料单价（供应链建设单位结存成本价格）
	GetProjectAuditMaterialPrice(pk_outbound string, acode string) ([]models.MaterialPrice, error) // 获取审计材料单价（供应链出库价）
	GetApprovalCodeByName(projectName string) (string, error)                                      // 根据项目名称获取审批编码
}

type scmDaoImpl struct {
	dbScm *sqlx.DB
}

func NewScmDao(dbSCM *sqlx.DB) ScmDao {
	return &scmDaoImpl{
		dbScm: dbSCM,
	}
}

// 获取最近一次采购价格，供预算使用
func (dao *scmDaoImpl) GetMaterialPurchasePrice(materialPrices []models.MaterialPrice) (int, error) {
	if len(materialPrices) == 0 {
		return 0, nil
	}

	// 1. 提取材料编码
	materialCodes := make([]string, 0, len(materialPrices))
	for _, material := range materialPrices {
		materialCodes = append(materialCodes, material.MaterialCode)
	}

	// 2. 构建基础查询（使用Oracle的命名占位符）
	baseQuery := `
        SELECT rmv.MATERIAL_CODE,rmv.LASTPRICE 
        FROM dt_po_ddlastprice rmv
        WHERE rmv.pk_org = :1 AND rmv.material_code IN (`

	// 3. 为Oracle手动构建IN子句的占位符 (:2, :3, :4, ...)
	placeholders := make([]string, len(materialCodes))
	for i := range placeholders {
		placeholders[i] = fmt.Sprintf(":%d", i+2) // 从:2开始，因为:1是PK_ORG
	}

	// 4. 组合完整查询
	query := baseQuery + strings.Join(placeholders, ", ") + ")"

	// 5. 准备参数（顺序必须与占位符一致）
	args := make([]interface{}, 0)
	args = append(args, "0001A810000000000HME") // 市公司出库表PK_ORG，对应 :1
	for _, code := range materialCodes {
		args = append(args, code) // 对应 :2, :3, :4, ...
	}

	// 6. 执行查询
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	var dbResults []models.MaterialLastPrice
	err := dao.dbScm.SelectContext(ctx, &dbResults, query, args...)
	if err != nil {
		logger.Errorf("GetMaterialPurchasePrice SelectContext failed - error: %v", err)
		return 0, fmt.Errorf("database query failed: %w", err)
	}

	// 7. 创建材料编码到查询结果的映射，便于快速查找
	priceMap := make(map[string]models.MaterialLastPrice)
	for _, result := range dbResults {
		priceMap[result.MaterialCode] = result
	}

	// 8. 将查询结果写回到原始 materials 切片中
	updatedCount := 0
	for i := range materialPrices {
		materialCode := materialPrices[i].MaterialCode
		if result, exists := priceMap[materialCode]; exists {
			materialPrices[i].Price = result.LastPrice
			updatedCount++
		} else {
			logger.Warnf("Warning: Price not found for material code: %s", materialCode)
		}
	}

	return updatedCount, nil
}

// 获取最新结存成本价格，供结算使用
func (dao *scmDaoImpl) GetMaterialBalancePrice(pkorg_reallib string, materialPrices []models.MaterialPrice) (int, error) {
	if len(materialPrices) == 0 {
		return 0, nil
	}

	// 1. 提取材料编码
	materialCodes := make([]string, 0, len(materialPrices))
	for _, material := range materialPrices {
		materialCodes = append(materialCodes, material.MaterialCode)
	}

	// 2. 构建基础查询（使用Oracle的命名占位符）
	baseQuery := `
        SELECT rmv.MATERIAL_CODE,rmv.NABPRICE 
        FROM sdgd.LCJK_IA_ZXCBJ rmv
        WHERE rmv.pk_org = :1 AND rmv.material_code IN (`

	// 3. 为Oracle手动构建IN子句的占位符 (:2, :3, :4, ...)
	placeholders := make([]string, len(materialCodes))
	for i := range placeholders {
		placeholders[i] = fmt.Sprintf(":%d", i+2) // 从:2开始，因为:1是PK_ORG
	}

	// 4. 组合完整查询
	query := baseQuery + strings.Join(placeholders, ", ") + ")"

	// 5. 准备参数（顺序必须与占位符一致）
	args := make([]interface{}, 0)
	args = append(args, pkorg_reallib) // 建设单位实体库PK_ORG，对应 :1
	for _, code := range materialCodes {
		args = append(args, code) // 对应 :2, :3, :4, ...
	}

	// 6. 执行查询
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	var dbResults []models.MaterialNabPrice
	err := dao.dbScm.SelectContext(ctx, &dbResults, query, args...)
	if err != nil {
		logger.Errorf("GetMaterialBalancePrice SelectContext failed - error: %v", err)
		return 0, fmt.Errorf("database query failed: %w", err)
	}

	// 7. 创建材料编码到查询结果的映射，便于快速查找
	priceMap := make(map[string]models.MaterialNabPrice)
	for _, result := range dbResults {
		priceMap[result.MaterialCode] = result
	}

	// 8. 将查询结果写回到原始 materials 切片中
	updatedCount := 0
	for i := range materialPrices {
		materialCode := materialPrices[i].MaterialCode
		if result, exists := priceMap[materialCode]; exists {
			materialPrices[i].Price = result.NabPrice
			updatedCount++
		} else {
			logger.Warnf("Warning: Balance Price not found for material code: %s", materialCode)
		}
	}

	return updatedCount, nil
}

func (dao *scmDaoImpl) GetApprovalCodeByName(projectName string) (string, error) {
	query := "select PROJECT_CODE from SDGD.LCJK_BD_XM WHERE PROJECT_NAME LIKE :1"
	var approvalCode string
	err := dao.dbScm.Get(&approvalCode, query, "%"+projectName+"%")
	if err != nil {
		if err == sql.ErrNoRows {
			return "", fmt.Errorf("no approval code found for project name: %s", projectName)
		}
		logger.Errorf("GetApprovalCodeByName failed - projectName: %s, error: %v", projectName, err)
		return "", fmt.Errorf("failed to get approval code: %w", err)
	}
	return approvalCode, nil
}

func (dao *scmDaoImpl) GetProjectAuditMaterialPrice(pk_outbound string, acode string) ([]models.MaterialPrice, error) {
	var materialPrices []models.MaterialPrice
	query := "select MATERIAL_CODE, PRICE from sdgd.LCJK_IC_MATERIA where PK_ORG = :1 and AC_CODE = :2"
	err := dao.dbScm.Select(&materialPrices, query, pk_outbound, acode)
	if err != nil {
		logger.WithFields(map[string]interface{}{
			"layer":       "dao",
			"operation":   "scmDaoImpl.GetProjectAuditMaterialPrice",
			"pk_outbound": pk_outbound,
			"acode":       acode,
		}).Errorf("查询项目出库单失败 - error: %v", err)
		return nil, fmt.Errorf("database query failed: %w", err)
	}
	return materialPrices, nil
}
